﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Collections;
using System.Configuration;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.OleDb;
using System.Linq;
using System.Text;



namespace Clinica_Frba.DataRepository
{
    public abstract class RepositoryBase
    {
        static string NOMBRE_ESQUEMA = "vacuna_gdd.";
        static string SERVER = "localhost\\SQLSERVER2008";
        static string USERNAME = "gd";
        static string PASSWORD = "gd2013";
        static string DATABASE = "GD2C2013";
        static string errorDataReader = "MultipleActiveResultSets = true";
        static string connectionString = "Data Source=" + SERVER + ";" + "User ID=" + USERNAME + ";" + "Password=" + PASSWORD + ";" + "Initial Catalog=" + DATABASE + ";" + errorDataReader;

        private static SqlConnection _conect = new SqlConnection();

        public static SqlConnection connection()
        {
            if (_conect.State == ConnectionState.Closed)
            {
                _conect.ConnectionString = connectionString;//@System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
                Console.WriteLine("Antes del error: "+@System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);
                
                _conect.Open();
            }
            
            return _conect;
        }



        /**
         * Ejecuta el stored procedure que le pasamos como parametro, con el listado de parametros que le pasamos.
         */
        protected void ejecutarStoredProcedure(string nombreStoredProcedure, List<SqlParameter> parametros)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    connection.Open();
                    command.Connection = connection;
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    command.CommandText = NOMBRE_ESQUEMA + nombreStoredProcedure;
                    agregarParametros(command.Parameters, parametros);
                    command.ExecuteReader();
                    connection.Close();
                }
            }
        }

        void agregarParametros(SqlParameterCollection parameters, List<SqlParameter> parametros)
        {
            foreach (SqlParameter cadaParametro in parametros)
            {
                parameters.Add(cadaParametro);
            }
        }

        /**
         * Ejecuta el stored procedure que le pasamos como parámetro y retorna un DataSet con los resultados
         * permitiendo asociarlo con un elemento de la vista como por ejemplo un CheckedListBox.
         */
        public DataSet obtenerDataSetParaVincularElementoVista(string nombreStoredProcedure)
        {
            DataSet dataSet = new System.Data.DataSet();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    connection.Open();

                    command.Connection = connection;
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    command.CommandText = NOMBRE_ESQUEMA + nombreStoredProcedure;

                    SqlDataAdapter adapter = new SqlDataAdapter(command);

                    adapter.Fill(dataSet);

                    connection.Close();
                }
            }
            return dataSet;
        }


         /**
         * Se realiza la consulta a la base de datos
         * para leer la base segun la consulta realizada.
         */

        public static SqlDataReader realizarConsulta(string commandtext, string commandtype, List<SqlParameter> ListaParametro)
        {

            SqlCommand comando = new SqlCommand();
            comando.Connection = connection();
            comando.CommandText = commandtext;
            foreach (SqlParameter elemento in ListaParametro)
            {
                comando.Parameters.Add(elemento);
            }
            switch (commandtype)
            {
                case "Texto":
                    comando.CommandType = CommandType.Text;
                    break;
                case "TD":
                    comando.CommandType = CommandType.TableDirect;
                    break;
                case "SP":
                    comando.CommandType = CommandType.StoredProcedure;
                    break;
            }
            return comando.ExecuteReader();
            
        }



        public static bool InsertarElementos(string commandtext, string commandtype, List<SqlParameter> ListaParametro)
        {
            SqlCommand comando = new SqlCommand();
            comando.Connection = connection();
            comando.CommandText = commandtext;
            foreach (SqlParameter elemento in ListaParametro)
            {
                comando.Parameters.Add(elemento);
            }
            switch (commandtype)
            {
                case "T":
                    comando.CommandType = CommandType.Text;
                    break;
                case "SP":
                    comando.CommandType = CommandType.StoredProcedure;
                    break;
            }
            try
            {
                comando.ExecuteNonQuery();
                return true;
            }
            catch
            { return false; }
        }



        public static decimal ExecuteSP(string commandtext, List<SqlParameter> ListaParametro)
        {
            try
            {
                SqlCommand comando = new SqlCommand();
                comando.Connection = connection();
                comando.CommandText = commandtext;
                comando.CommandType = CommandType.StoredProcedure;

                foreach (SqlParameter elemento in ListaParametro)
                {
                    comando.Parameters.Add(elemento);
                }

                comando.ExecuteNonQuery();
                return (decimal)comando.Parameters["@ret"].Value;
            }
            catch
            {
                return 0;
            }
        }
    }
}
